package com.librarySys.librarySys.Mapper;


import com.librarySys.librarySys.Bean.*;
import org.apache.ibatis.annotations.*;
import org.springframework.boot.web.servlet.ServletComponentScan;

import java.util.Date;
import java.util.List;


@Mapper
public interface CommonMapper {
    @Select("select * from authority")
    public List<Authority> getAllAuthority();

    @Select("select * from role")
    public List<Role> getAllRole();

    @Insert("insert into user(username,password,sex,email,telphone,role_id) values(#{username},#{password},#{sex},#{email},#{telphone},#{roleId})")
    public int insertUser(User user);

    @Update("update user set username=#{username},sex=#{sex},email=#{email},telphone=#{telphone} where user_id = #{userId}")
    public int updateUserByUserId(User user);

    @Update("update user set role_id=#{arg0} where user_id=#{arg1}")
    public int updateUserRole(Integer roleId, Integer userId) throws Exception;

    @Update("update user set password=#{arg0} where user_id=#{arg1}")
    public int updateUserPassword(String password, Integer userId);

    @Select("select * from user where username=#{username}")
    public User getUserByUserName(String username);

    @Select("select user_id,username,email,sex,telphone,role_id from user where user_id=#{userId}")
    public User getUserByUserId(Integer userId);

    @Select("select user_id,username,email,sex,telphone,role_id from user where user_id like #{keyword} or username like #{keyword}")
    public List<User> getUserBySearch(String keyword);

    @Select("select * from user")
    public List<User> getAllUser();

    @Delete("delete from user where user_id = #{userId}")
    public int deleteUserByUserId(Integer userId);

    @Insert("insert into book_type(title,author,publisher,details) values(#{title},#{author},#{publisher},#{details})")
    public int insertBookType(BookType bookType);

    @Select("select * from book_type")
    public List<BookType> getAllBookType();

    @Insert("insert into book(book_type_id,state)values(#{bookTypeId},0)")
    public int insertBook(Integer bookTypeId);

    // 根据typeid查到某一类的书籍的所有信息
    @Select("select book.book_id,title,author,publisher,details,state from book,book_type where book.book_type_id=book_type.book_type_id and book.book_type_id=#{bookTypeId}")
    public List<Book> getBookByBookTypeId(Integer bookTypeId);

    // 这里是有别名的，涉及到多表查询
    @Select("select book_type.book_type_id,title,author,publisher,details,count(book.book_id) count from book_type left join book on book.book_type_id=book_type.book_type_id group by book_type.book_type_id;")
    public List<BookSet> getAllBookSet();

    // 获取到用户的所有权限
    @Select("select authority.authority_id authority_id,authority.auth auth from user,authority,role_to_authority where user.user_id=#{userId} AND role_to_authority.authority_id=authority.authority_id AND user.role_id=role_to_authority.role_id")
    public List<Authority> getAuthorityByUserId(Integer userId);

    @Delete("delete from book where book_id=#{bookId}")
    public int deleteBookById(Integer bookId);

    @Delete("delete from book_type where book_type_id=#{bookTypeId}")
    public int deleteBookTypeById(Integer bookTypeId);


    @Update("update book set state=1 where book_id=#{bookId}")
    public int updateBookStateByBookId(Integer bookId);

    @Update("update book set state=0 where book_id=#{bookId}")
    public int updateReturnBookStateByBookId(Integer bookId);

    @Insert("insert into borrow_record(book_id,user_id,borrow_time,state) values(#{bookId},#{userId},#{borrowTime},1)")
    public int insertBorrowRecord(BookRecord bookRecord);

    @Select("select * from book_type where book_type_id=#{bookTypeId}")
    public BookType getBookTypeByBookTypeId(Integer bookTypeId);

    @Update("update book_type set title=#{title}, author=#{author}, publisher=#{publisher}, details=#{details} where book_type_id=#{bookTypeId}")
    public int updateBookType(BookType bookType);

    @Select("select * from borrow_record")
    public List<BookRecord> getAllBorrowRecord();

    @Update("update borrow_record set state=0 where book_id=#{bookId} and state=1")
    public int updateBorrowRecordByBookId(Integer bookId);

    @Select("select username,user.user_id,borrow_id,book.book_id,title,book.state,borrow_time from borrow_record,book,book_type,user where borrow_record.book_id=book.book_id and book.book_type_id=book_type.book_type_id and user.user_id=borrow_record.user_id")
    public List<BorrowRecordInfo> getAllBorrowRecordInfo();

    @Select("select username,user.user_id,borrow_id,book.book_id,title,book.state,borrow_time from borrow_record,book,book_type,user where borrow_record.book_id=book.book_id and book.book_type_id=book_type.book_type_id and user.user_id=borrow_record.user_id and username=#{username}")
    public List<BorrowRecordInfo> getBorrowRecordInfoByUserName(String username);

    @Select("select book_type.book_type_id,title,author,publisher,details,count(book.book_id) count from book_type left join book on book.book_type_id=book_type.book_type_id group by book_type.book_type_id order by count desc")
    public List<BookSet> getAllBookSetByOrder();

    @Select("select book_type.book_type_id,title,author,publisher,details,count(book.book_id) count from book_type left join book on book.book_type_id=book_type.book_type_id group by book_type.book_type_id having title like #{keyword} or details like #{keyword} or author like #{keyword} or publisher like #{keyword}")
    public List<BookSet> getAllBookSetBySearch(String keyword);
}